package com.mycom.dao;

import com.mycom.entity.Student;
import com.mycom.util.DbUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

//数据访问对象 data access object：包含针对一张表的增 删 改 查 方法
public class StudentDao {
    //添加  添加除主键之外的所有列
    public Integer addStudent(Student stu){
        Connection  cn  = DbUtil.getConn();
        try {
            String  insert = "insert into student (stuno,stuname,stusex,stuage,stubirth,stuscore) values (?,?,?,?,?,?)";
            PreparedStatement ps =cn.prepareStatement(insert);
            //创建执行增删改查的工具，以及为语句中的 ? 注入数据
            ps.setString(1,stu.getStuno());
            ps.setString(2,stu.getStuname());
            ps.setString(3,stu.getStusex());
            ps.setInt(4,stu.getStuage());
            ps.setDate(5,stu.getStubirth());
            //ps.setInt(6,stu.getStuscore());
            ps.setObject(6,null);//将数据给为null
            int result = ps.executeUpdate();//增删改 都是对表的修改,返回影响的行数
            return result;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtil.closeConn();
        }
    }

    //修改  以id为条件 修改其他所有列
    public Integer updateStudent(Student stu){
        return 0;
    }

    //删除
    public Integer deleteStudent(Integer sid){
        return 0;
    }

    //多行结果查询
    public List<Student> queryStudent(String sex,Integer age){
        //使用 集合List 保存查询结果的多个对象
        ArrayList<Student> students = new ArrayList<Student>();
        Connection  cn  = DbUtil.getConn();
        try {
           String select = "select * from student where stusex= ? and stuage < ?";
           PreparedStatement preparedStatement = cn.prepareStatement(select);
           preparedStatement.setString(1,sex);
           preparedStatement.setInt(2,age);
           ResultSet rs = preparedStatement.executeQuery();
            while(rs.next()){
                //将查询结果的每行数据 封装为对象 并加入到集合内
                Student stu = new  Student(
                        rs.getInt("sid"),
                        rs.getString("stuno"),
                        rs.getString("stuname"),
                        rs.getString("stusex"),
                        rs.getInt("stuage"),
                        rs.getDate("stubirth"),
                        rs.getInt("stuscore")
                );
                students.add(stu);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtil.closeConn();
        }

        return students;
    }

    //根据ID查询单个数据
    public Student queryStudentById(Integer sid){
        Connection  cn  = DbUtil.getConn();
        try {
            String select = "select * from student where sid= ?";
            PreparedStatement preparedStatement = cn.prepareStatement(select);
            preparedStatement.setInt(1,sid);
            ResultSet rs = preparedStatement.executeQuery();
            if(rs.next()){
                //将查询结果的每行数据 封装为对象 并加入到集合内
                Student stu = new  Student(
                        rs.getInt("sid"),
                        rs.getString("stuno"),
                        rs.getString("stuname"),
                        rs.getString("stusex"),
                        rs.getInt("stuage"),
                        rs.getDate("stubirth"),
                        rs.getInt("stuscore")
                );
                return  stu;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtil.closeConn();
        }
        return null;
    }

}
